


--use PolePosition

-- // checksum failed custom error
--exec sp_addmessage @msgnum = 505050, @severity = 16, @msgtext = N'%s(%s) checksum compare failed', @replace = 'REPLACE', @lang = 'us_english'
GO

-- // not found custom error
--exec sp_addmessage @msgnum = 505060, @severity = 16, @msgtext = N'%s(%s) does not exist', @replace = 'REPLACE', @lang = 'us_english'
GO

-- // unit test custom error
--exec sp_addmessage @msgnum = 505070, @severity = 16, @msgtext = N'%s Test Failed. Expected< %s > Actual< %s > %s', @replace = 'REPLACE', @lang = 'us_english'
GO



--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'v_tb_Utility_Contacts') and OBJECTPROPERTY(id, N'IsProcedure') = 0)
drop view v_tb_Utility_Contacts
GO

create view v_tb_Utility_Contacts as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: Selected record(s) from the table; this view is the source for all procedures' select statements
	*/
	
select
	 [Utility_ContactId] as [Utility_ContactId]
	,[UtilityFK] as [UtilityFK]
	,[Utility_Contact_TypeFK] as [Utility_Contact_TypeFK]
	,[Name] as [Name]
	,[UserName] as [UserName]
	,[SecurityRole] as [SecurityRole]
	,[Password] as [Password]
	,[Enabled] as [Enabled]
	-- customization may be required for FriendlyName
	,Left(CONVERT(varchar(255), [Name]), 255) as [FriendlyName]
from
	tb_Utility_Contacts
GO





--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_UtilityContact_Test') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_UtilityContact_Test
GO


create proc up_UtilityContact_Test
	@Utility_ContactId int
as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: Test if a record exists, using the primary key
	*/

SET NOCOUNT ON	
	
if exists(
	select [Utility_ContactId] 
	from [tb_Utility_Contacts] source
	where [source].[Utility_ContactId] = @Utility_ContactId)
begin
	return(1)
end
else
begin
	return(0)
end
GO






--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_UtilityContact_Select') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_UtilityContact_Select
GO

create proc up_UtilityContact_Select
	 @Utility_ContactId int = null 
	,@UtilityFK int = null 
	,@Utility_Contact_TypeFK int = null 
	,@Name nvarchar(50) = null 
	,@UserName nvarchar(50) = null 
	,@SecurityRole nvarchar(50) = null 
	,@Password varchar(50) = null 
	,@Enabled bit = null 
as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: Selected record(s) based on the passed arguments; no arguments returns all
	*/

SET NOCOUNT ON	

select * from v_tb_Utility_Contacts
where
		IsNull([v_tb_Utility_Contacts].[Utility_ContactId], -1) = Coalesce(@Utility_ContactId, [v_tb_Utility_Contacts].[Utility_ContactId], -1)
	AND	IsNull([v_tb_Utility_Contacts].[UtilityFK], -1) = Coalesce(@UtilityFK, [v_tb_Utility_Contacts].[UtilityFK], -1)
	AND	IsNull([v_tb_Utility_Contacts].[Utility_Contact_TypeFK], -1) = Coalesce(@Utility_Contact_TypeFK, [v_tb_Utility_Contacts].[Utility_Contact_TypeFK], -1)
	AND	IsNull([v_tb_Utility_Contacts].[Name], '') = Coalesce(@Name, [v_tb_Utility_Contacts].[Name], '')
	AND	IsNull([v_tb_Utility_Contacts].[UserName], '') = Coalesce(@UserName, [v_tb_Utility_Contacts].[UserName], '')
	AND	IsNull([v_tb_Utility_Contacts].[SecurityRole], '') = Coalesce(@SecurityRole, [v_tb_Utility_Contacts].[SecurityRole], '')
	AND	IsNull([v_tb_Utility_Contacts].[Password], '') = Coalesce(@Password, [v_tb_Utility_Contacts].[Password], '')
	AND	IsNull([v_tb_Utility_Contacts].[Enabled], 0) = Coalesce(@Enabled, [v_tb_Utility_Contacts].[Enabled], 0)
GO





--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_UtilityContact_Select_Top1') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_UtilityContact_Select_Top1
GO

create proc up_UtilityContact_Select_Top1
	 @Utility_ContactId int = null 
	,@UtilityFK int = null 
	,@Utility_Contact_TypeFK int = null 
	,@Name nvarchar(50) = null 
	,@UserName nvarchar(50) = null 
	,@SecurityRole nvarchar(50) = null 
	,@Password varchar(50) = null 
	,@Enabled bit = null 
as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: Selected top 1 record based on the passed arguments; no arguments returns all
	*/

SET NOCOUNT ON	

select TOP 1 * from v_tb_Utility_Contacts
where
		IsNull([v_tb_Utility_Contacts].[Utility_ContactId], -1) = Coalesce(@Utility_ContactId, [v_tb_Utility_Contacts].[Utility_ContactId], -1)
	AND	IsNull([v_tb_Utility_Contacts].[UtilityFK], -1) = Coalesce(@UtilityFK, [v_tb_Utility_Contacts].[UtilityFK], -1)
	AND	IsNull([v_tb_Utility_Contacts].[Utility_Contact_TypeFK], -1) = Coalesce(@Utility_Contact_TypeFK, [v_tb_Utility_Contacts].[Utility_Contact_TypeFK], -1)
	AND	IsNull([v_tb_Utility_Contacts].[Name], '') = Coalesce(@Name, [v_tb_Utility_Contacts].[Name], '')
	AND	IsNull([v_tb_Utility_Contacts].[UserName], '') = Coalesce(@UserName, [v_tb_Utility_Contacts].[UserName], '')
	AND	IsNull([v_tb_Utility_Contacts].[SecurityRole], '') = Coalesce(@SecurityRole, [v_tb_Utility_Contacts].[SecurityRole], '')
	AND	IsNull([v_tb_Utility_Contacts].[Password], '') = Coalesce(@Password, [v_tb_Utility_Contacts].[Password], '')
	AND	IsNull([v_tb_Utility_Contacts].[Enabled], 0) = Coalesce(@Enabled, [v_tb_Utility_Contacts].[Enabled], 0)
GO




--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_UtilityContact_Insert') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_UtilityContact_Insert
GO

create proc up_UtilityContact_Insert
	 @Utility_ContactId int =null OUTPUT  
	,@UtilityFK int  
	,@Utility_Contact_TypeFK int  
	,@Name nvarchar(50)  
	,@UserName nvarchar(50)  
	,@SecurityRole nvarchar(50) = null  
	,@Password varchar(50) = null  
	,@Enabled bit  
as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: Inserts a new record; missing arguments are set as their default (typically null)
	*/

SET NOCOUNT ON	
--int

insert tb_Utility_Contacts 
	(
	
	 [UtilityFK] 
	,[Utility_Contact_TypeFK] 
	,[Name] 
	,[UserName] 
	,[SecurityRole] 
	,[Password] 
	,[Enabled] 
	)
	values
	(
	
	 @UtilityFK 
	,@Utility_Contact_TypeFK 
	,@Name 
	,@UserName 
	,@SecurityRole 
	,@Password 
	,@Enabled 
	)
set @Utility_ContactId = scope_identity()
select * from v_tb_Utility_Contacts where Utility_ContactId = @Utility_ContactId

GO




--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_UtilityContact_Update') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_UtilityContact_Update
GO


create proc up_UtilityContact_Update
	 @Utility_ContactId int  
	,@UtilityFK int = null  
	,@Utility_Contact_TypeFK int = null  
	,@Name nvarchar(50) = null  
	,@UserName nvarchar(50) = null  
	,@SecurityRole nvarchar(50) = null  
	,@Password varchar(50) = null  
	,@Enabled bit = null  
	,@OriginalUtility_ContactId int  = null --Original PrimaryKey
	
as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: Updates an existing record, verifying exist; missing arguments persist original record value
		NOTE: this version of the update allows up to write nulls to the table.
	*/

SET NOCOUNT ON	

if @OriginalUtility_ContactId is null
	set @OriginalUtility_ContactId = @Utility_ContactId

-- test
declare @Result bit
execute @Result = up_UtilityContact_Test @OriginalUtility_ContactId
if (not @Result = 1)
begin
	Declare @x varchar(100)
	Set @x = Convert(Varchar(100), @Utility_ContactId)
	raiserror(505060, 16, 1, 'UtilityContact', @x)
	return
end

-- proceed
update tb_Utility_Contacts 
set

	 [UtilityFK] = IsNull(@UtilityFK, [source].[UtilityFK])
	,[Utility_Contact_TypeFK] = IsNull(@Utility_Contact_TypeFK, [source].[Utility_Contact_TypeFK])
	,[Name] = IsNull(@Name, [source].[Name])
	,[UserName] = IsNull(@UserName, [source].[UserName])
	,[SecurityRole] =  @SecurityRole
	,[Password] =  @Password
	,[Enabled] = IsNull(@Enabled, [source].[Enabled])
	
from tb_Utility_Contacts source
where
	[source].[Utility_ContactId] = @OriginalUtility_ContactId


select * from v_tb_Utility_Contacts where Utility_ContactId = @Utility_ContactId
GO





--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_UtilityContact_Delete') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_UtilityContact_Delete
GO


create proc up_UtilityContact_Delete
	@Utility_ContactId int
as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: Deletes an existing record, verifying exist
	*/

SET NOCOUNT ON	

-- test
declare @Result bit
execute @Result = up_UtilityContact_Test @Utility_ContactId
if (not @Result = 1)
begin
	Declare @x varchar(100)
	Set @x = Convert(Varchar(100), @Utility_ContactId)
	raiserror(505060, 16, 1, 'UtilityContact', @x)
	return
end

-- proceed
delete from tb_Utility_Contacts 
where [Utility_ContactId] = @Utility_ContactId

GO





--use PolePosition

-- unit test procs

if exists (select * from dbo.sysobjects where id = object_id(N'DevOnly_UtilityContact_UnitTestInitialize') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure DevOnly_UtilityContact_UnitTestInitialize
GO

create proc DevOnly_UtilityContact_UnitTestInitialize as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: Preps the table for testing, including removing constraints
		Note: Can (probably should) be deleted in production environment
	*/

Declare @exe varchar(1000)

-- remove any existing constraints (where this is the foreign table)
Set @exe = 'ALTER TABLE [tb_Utility_Contacts] DROP CONSTRAINT FK_tb_Utility_Contacts_tb_Utilities'
Print @exe
Execute(@exe)
Set @exe = 'ALTER TABLE [tb_Utility_Contacts] DROP CONSTRAINT FK_tb_Utility_Contacts_tb_Utility_Contact_Types'
Print @exe
Execute(@exe)

-- remove any existing constraints (where this is the primary table)
Set @exe = 'ALTER TABLE [tb_Utility_QueryAssets] DROP CONSTRAINT FK_tb_Utility_QueryAssets_tb_Utility_Contacts'
Print @exe
Execute(@exe)
Set @exe = 'ALTER TABLE [tb_Utility_Asset_Distributions] DROP CONSTRAINT FK_tb_Utility_Asset_Distributions_tb_Utility_Contacts'
Print @exe
Execute(@exe)
Set @exe = 'ALTER TABLE [tb_Utility_Asset_XMissionStructures] DROP CONSTRAINT FK_tb_Utility_Asset_XMissionStructures_tb_Utility_Contacts'
Print @exe
Execute(@exe)
Set @exe = 'ALTER TABLE [tb_Utility_Asset_Meters] DROP CONSTRAINT FK_tb_Utility_Asset_Meters_tb_Utility_Contacts'
Print @exe
Execute(@exe)

--Disable Unique Indexes

GO




--use PolePosition

-- unit test procs

if exists (select * from dbo.sysobjects where id = object_id(N'DevOnly_UtilityContact_UnitTestClear') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure DevOnly_UtilityContact_UnitTestClear
GO

create proc DevOnly_UtilityContact_UnitTestClear as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: Removes all records
	*/

delete from tb_Utility_Contacts;

GO




--use PolePosition

-- unit test procs

if exists (select * from dbo.sysobjects where id = object_id(N'DevOnly_UtilityContact_UnitTestTestData') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure DevOnly_UtilityContact_UnitTestTestData
GO

create proc DevOnly_UtilityContact_UnitTestTestData as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: clears the database and adds test data
		Note: Can (probably should) be deleted in production environment
	*/

-- remove existing records

Execute DevOnly_UtilityContact_UnitTestClear;

-- insert new records

execute up_UtilityContact_Insert
		 @UtilityFK = 535910536
		,@Utility_Contact_TypeFK = 388916781
		,@Name = 'AD0EU4AABAAA457A6AAA6A2AY1S13J8T64A21SWD5HA3VA3QAD'
		,@UserName = 'ACARA5AAA22AOVAAS6OUPF78V3AU1L44A8120AAKCK7U7AT0A2'
		,@SecurityRole = '16M521AA6AAS5PHA68X23AFA4A7A01ANA5AEAQK11A6A4CYAAM'
		,@Password = 'A6A1VA6AAS1JAX0AAA8A8A5563321A0KL1QIHBVUAAAAW4APGA'
		,@Enabled = 0

execute up_UtilityContact_Insert
		 @UtilityFK = 792733333
		,@Utility_Contact_TypeFK = 419880622
		,@Name = 'AU4K4A33KCAKOA8I6AAOYVA57AA3L8KAA71AVHY67AA77A33D3'
		,@UserName = 'AUJAAHMS4GAOVJ41SS81YACAAAAJX1JP72RAA6NCAAAAM5F01A'
		,@SecurityRole = '05AIADAAQ3A85W2AYA7AER71U8M87457AJAA0AADRW0RFA773A'
		,@Password = 'AHE42ON0AGYLVNAA6ARBVA18UAAL4PJ7A84AOUAMA6N8VRQDQI'
		,@Enabled = 0

execute up_UtilityContact_Insert
		 @UtilityFK = 558911747
		,@Utility_Contact_TypeFK = 1661583779
		,@Name = '0QAAAAAXJBIJAAHU60RAA08KLKWLAU4AA6CTA026ALL45A7AO1'
		,@UserName = '1AAIUA5PA2LA8AA4LAAVABA5A5A6UFA12AAAP0N2VRIJ6T4RGA'
		,@SecurityRole = '53PDAAD1AFARC257B3S3BAAAAARA5RY8HNAA8AAAM40AA57PAA'
		,@Password = 'BAA8AD3W1AAXCAAOVA3AVAVS6481AA2VNULSAPO0TAA3AA6AA6'
		,@Enabled = 1

execute up_UtilityContact_Insert
		 @UtilityFK = 1875604173
		,@Utility_Contact_TypeFK = 155183962
		,@Name = 'FAAAX8Q73AA2AAA4AAAJ0ANJAOIK4TAAAA6MSATAA66AXIJJVN'
		,@UserName = '0IASA4ORAA7EL8AKAP7O6784EAADAVXOA7AA5AUUA2603F0AAH'
		,@SecurityRole = 'QWIA6A0745AAA8AA3AAAAWAAWAAAYA4AVAQ7A0A7S6A36A47EA'
		,@Password = '3C4I5S0GAA4AAAA1QAAANPR13T6AAFAKUAA571AVY252X2TOAA'
		,@Enabled = 1

execute up_UtilityContact_Insert
		 @UtilityFK = 1286893787
		,@Utility_Contact_TypeFK = 496666111
		,@Name = 'KAAAA1A70ALNAAWAT21AG2WA4VT0RMA74NAA4AC74S5A2FA7T7'
		,@UserName = 'MAA37ATA26ADN1A563SDAN3AUA4OAAYAJAA3YJFAA2DIBM6Y6A'
		,@SecurityRole = 'T0A8A3A2301AP7M881AK1FAAU0AAAAAA1EAAA1J8I8I3UUU360'
		,@Password = '5BA0RAA11UA853IA5AGA5A2MAQN564AA4A0F02A4W487RMTB5N'
		,@Enabled = 0



GO





--use PolePosition

-- unit test procs

if exists (select * from dbo.sysobjects where id = object_id(N'DevOnly_UtilityContact_UnitTestCleanUp') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure DevOnly_UtilityContact_UnitTestCleanUp
GO

create proc DevOnly_UtilityContact_UnitTestCleanUp as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: Clears table and inserts dummy data for testing purposes
		Note: Can (probably should) be deleted in production environment
	*/

Declare @exe varchar(1000)

-- re-add constraint (where this table is the foreign)

Set @exe = 'if not exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[FK_tb_Utility_Contacts_tb_Utilities]'') and OBJECTPROPERTY(id, N''IsConstraint'') = 1) ALTER TABLE [tb_Utility_Contacts] ADD CONSTRAINT [FK_tb_Utility_Contacts_tb_Utilities] FOREIGN KEY ([UtilityFK]) REFERENCES [tb_Utilities] ([UtilityId]) NOT FOR REPLICATION'
Print @exe
Execute(@exe)

Set @exe = 'ALTER TABLE [tb_Utility_Contacts] NOCHECK CONSTRAINT [FK_tb_Utility_Contacts_tb_Utilities]'
Print @exe
Execute(@exe)

Set @exe = 'if not exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[FK_tb_Utility_Contacts_tb_Utility_Contact_Types]'') and OBJECTPROPERTY(id, N''IsConstraint'') = 1) ALTER TABLE [tb_Utility_Contacts] ADD CONSTRAINT [FK_tb_Utility_Contacts_tb_Utility_Contact_Types] FOREIGN KEY ([Utility_Contact_TypeFK]) REFERENCES [tb_Utility_Contact_Types] ([Utility_Contact_TypeId]) NOT FOR REPLICATION'
Print @exe
Execute(@exe)

Set @exe = 'ALTER TABLE [tb_Utility_Contacts] NOCHECK CONSTRAINT [FK_tb_Utility_Contacts_tb_Utility_Contact_Types]'
Print @exe
Execute(@exe)

-- re-add constraint (where this table is the primary)

Set @exe = 'if not exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[FK_tb_Utility_QueryAssets_tb_Utility_Contacts]'') and OBJECTPROPERTY(id, N''IsConstraint'') = 1) ALTER TABLE [tb_Utility_QueryAssets] ADD CONSTRAINT [FK_tb_Utility_QueryAssets_tb_Utility_Contacts] FOREIGN KEY ([Utility_ContactFK]) REFERENCES [tb_Utility_Contacts] ([Utility_ContactId]) NOT FOR REPLICATION'
Print @exe
Execute(@exe)

Set @exe = 'ALTER TABLE [tb_Utility_QueryAssets] NOCHECK CONSTRAINT [FK_tb_Utility_QueryAssets_tb_Utility_Contacts]'
Print @exe
--Execute(@exe)

Set @exe = 'if not exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[FK_tb_Utility_Asset_Distributions_tb_Utility_Contacts]'') and OBJECTPROPERTY(id, N''IsConstraint'') = 1) ALTER TABLE [tb_Utility_Asset_Distributions] ADD CONSTRAINT [FK_tb_Utility_Asset_Distributions_tb_Utility_Contacts] FOREIGN KEY ([Utility_ContactFK]) REFERENCES [tb_Utility_Contacts] ([Utility_ContactId]) NOT FOR REPLICATION'
Print @exe
Execute(@exe)

Set @exe = 'ALTER TABLE [tb_Utility_Asset_Distributions] NOCHECK CONSTRAINT [FK_tb_Utility_Asset_Distributions_tb_Utility_Contacts]'
Print @exe
--Execute(@exe)

Set @exe = 'if not exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[FK_tb_Utility_Asset_XMissionStructures_tb_Utility_Contacts]'') and OBJECTPROPERTY(id, N''IsConstraint'') = 1) ALTER TABLE [tb_Utility_Asset_XMissionStructures] ADD CONSTRAINT [FK_tb_Utility_Asset_XMissionStructures_tb_Utility_Contacts] FOREIGN KEY ([Utility_ContactFK]) REFERENCES [tb_Utility_Contacts] ([Utility_ContactId]) NOT FOR REPLICATION'
Print @exe
Execute(@exe)

Set @exe = 'ALTER TABLE [tb_Utility_Asset_XMissionStructures] NOCHECK CONSTRAINT [FK_tb_Utility_Asset_XMissionStructures_tb_Utility_Contacts]'
Print @exe
--Execute(@exe)

Set @exe = 'if not exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[FK_tb_Utility_Asset_Meters_tb_Utility_Contacts]'') and OBJECTPROPERTY(id, N''IsConstraint'') = 1) ALTER TABLE [tb_Utility_Asset_Meters] ADD CONSTRAINT [FK_tb_Utility_Asset_Meters_tb_Utility_Contacts] FOREIGN KEY ([Utility_ContactFK]) REFERENCES [tb_Utility_Contacts] ([Utility_ContactId]) NOT FOR REPLICATION'
Print @exe
Execute(@exe)

Set @exe = 'ALTER TABLE [tb_Utility_Asset_Meters] NOCHECK CONSTRAINT [FK_tb_Utility_Asset_Meters_tb_Utility_Contacts]'
Print @exe
--Execute(@exe)

--Reenable Unique Indexes
GO




--use PolePosition

-- unit test procs
if exists (select * from dbo.sysobjects where id = object_id(N'DevOnly_UtilityContact_UnitTest') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure DevOnly_UtilityContact_UnitTest
GO

create proc DevOnly_UtilityContact_UnitTest as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: Tests stored procedures 
	*/

	
SET NOCOUNT ON	

print 'START UNIT TEST'

declare @tablecount int
declare @viewcount int

declare @x varchar(100)
declare @y varchar(100)

-- test Clear

print 'Start Unit Test'

-- drop constraints

execute DevOnly_UtilityContact_UnitTestInitialize

-- bail on error

if (NOT @@error = 0)
	return(1)

-- clear table

execute DevOnly_UtilityContact_UnitTestClear

-- bail on error

if (NOT @@error = 0)
	return(1)

-- test table count calling table directly

select @tablecount = count(*) from tb_Utility_Contacts

if (NOT @tablecount = 0)
begin
	set @x = convert(varchar(100), 0)
	set @y = convert(varchar(100), @tablecount)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'tb_Utility_Contacts should contain 0 records after Clear.')
	-- return(1)
end

-- test table count calling the view

select @viewcount = count(*) from v_tb_Utility_Contacts

if (NOT @viewcount = 0)
begin
	set @x = convert(varchar(100), 0)
	set @y = convert(varchar(100), @viewcount)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'v_tb_Utility_Contacts should contain 0 records after Clear.')
	-- return(1)
end

print 'Start Test Insert'

-- test insert

execute up_UtilityContact_Insert
		 @UtilityFK = 956797278
		,@Utility_Contact_TypeFK = 153383091
		,@Name = 'OAAAAA42A4I3AAA8V0384DA458YV8AAWAAAA1BCAA05AAU3AAA'
		,@UserName = 'AA30AAAAAGN766HAXAWFA186185AAYXAAA275Y27A05AAA8JY2'
		,@SecurityRole = 'AX0UDADFPAWQ3DAQ8A073U6AA1IAFAAAP3A8E1A0A3AANN01M1'
		,@Password = 'A2IAA850AWAPYA7AA4AA44AS0AAXA86HOAAVLGRAYAA2D88AA4'
		,@Enabled = 1

-- bail on error

if (NOT @@error = 0)
	return(1)

-- test table count calling the table directly

select @tablecount = count(*) from tb_Utility_Contacts

if (NOT @tablecount = 1)
begin
	set @x = convert(varchar(100), 1)
	set @y = convert(varchar(100), @tablecount)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'tb_Utility_Contacts should contain 1 record after Insert.')
	-- return(1)
end

-- test table count calling the view

select @viewcount = count(*) from v_tb_Utility_Contacts

if (NOT @viewcount = 1)
begin
	set @x = convert(varchar(100), 1)
	set @y = convert(varchar(100), @viewcount)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'v_tb_Utility_Contacts should contain 1 record after Insert.')
	-- return(1)
end

print 'Start Test Insert Again'

-- test insert again


execute up_UtilityContact_Insert
		 @UtilityFK = 1590730922
		,@Utility_Contact_TypeFK = 1515351734
		,@Name = 'W71FAAT31AAT8AAQA5AQ0KAA0C7DNA18ASAAVA70A3AXQIAA5S'
		,@UserName = 'OTA6A1SA4MV014AO70A0OS36Y3LNF24A4MAA5AA7DASA420AHW'
		,@SecurityRole = '14AAAVA5AAA5J2BAAFR4A60J8BXVAMMWA5A38AA616A2A6ADAA'
		,@Password = '20RAAAAGFOLA620A4282SD0AKD2LA4Q0YPANJWJA7FAF2X88AA'
		,@Enabled = 1


-- bail on error

if (NOT @@error = 0)
	return(1)

-- test table count calling the table directly

select @tablecount = count(*) from tb_Utility_Contacts

if (NOT @tablecount = 2)
begin
	set @x = convert(varchar(100), 2)
	set @y = convert(varchar(100), @tablecount)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'tb_Utility_Contacts should contain 2 records after second Insert.')
	-- return(1)
end

-- test table count calling the view

select @viewcount = count(*) from v_tb_Utility_Contacts

if (NOT @viewcount = 2)
begin
	set @x = convert(varchar(100), 2)
	set @y = convert(varchar(100), @viewcount)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'v_tb_Utility_Contacts should contain 2 records after second Insert.')
	-- return(1)
end

print 'Start Test Update All'

-- test update

-- fetch the values from the first record, we'll be updating them



declare @Utility_ContactId_OldValue int


declare @UtilityFK_OldValue int


declare @Utility_Contact_TypeFK_OldValue int


declare @Name_OldValue nvarchar(50)


declare @UserName_OldValue nvarchar(50)


declare @SecurityRole_OldValue nvarchar(50)


declare @Password_OldValue varchar(50)


declare @Enabled_OldValue bit

select top 1
	 @Utility_ContactId_OldValue = Utility_ContactId
	,@UtilityFK_OldValue = UtilityFK
	,@Utility_Contact_TypeFK_OldValue = Utility_Contact_TypeFK
	,@Name_OldValue = Name
	,@UserName_OldValue = UserName
	,@SecurityRole_OldValue = SecurityRole
	,@Password_OldValue = Password
	,@Enabled_OldValue = Enabled
from tb_Utility_Contacts

-- generate random new data for the record

declare @UtilityFK_NewValue int
declare @Utility_Contact_TypeFK_NewValue int
declare @Name_NewValue nvarchar(50)
declare @UserName_NewValue nvarchar(50)
declare @SecurityRole_NewValue nvarchar(50)
declare @Password_NewValue varchar(50)
declare @Enabled_NewValue bit

set @UtilityFK_NewValue = 1687276114
set @Utility_Contact_TypeFK_NewValue = 1788168396
set @Name_NewValue = 'YAX42A515R6KFKYHDN58A22AAAAC33AA2G86FABTT61A83W87M'
set @UserName_NewValue = 'A66UGY8AA5P7A8DA2A4O3AAY2UW68AFA0QOG1A55F182ARAGYA'
set @SecurityRole_NewValue = '852AA5P2AATM3AX7LA3AALAA1A4A151A86ASX2BA3A5MA4AAAD'
set @Password_NewValue = 'AAAAKL55RQ7AFAAAIAO78EMHSRMMQ2AAAAAK0VAV87A4AA4ATJ'
if (@Enabled_OldValue = 1)
begin
	set @Enabled_NewValue = 0
end
else
begin
	set @Enabled_NewValue = 1
end

-- be sure that the random new data is different


if (@UtilityFK_OldValue = @UtilityFK_NewValue)
begin
	set @x = convert(varchar(100), @UtilityFK_OldValue)
	set @y = convert(varchar(100), @UtilityFK_NewValue)
	raiserror(505070, 16, 2, 'AreNotEqual', @x, @y, 'RDM @UtilityFK_OldValue should not equal @UtilityFK_NewValue; should be random new.')
	-- return(1)
end

if (@Utility_Contact_TypeFK_OldValue = @Utility_Contact_TypeFK_NewValue)
begin
	set @x = convert(varchar(100), @Utility_Contact_TypeFK_OldValue)
	set @y = convert(varchar(100), @Utility_Contact_TypeFK_NewValue)
	raiserror(505070, 16, 2, 'AreNotEqual', @x, @y, 'RDM @Utility_Contact_TypeFK_OldValue should not equal @Utility_Contact_TypeFK_NewValue; should be random new.')
	-- return(1)
end

if (@Name_OldValue = @Name_NewValue)
begin
	set @x = convert(varchar(100), @Name_OldValue)
	set @y = convert(varchar(100), @Name_NewValue)
	raiserror(505070, 16, 2, 'AreNotEqual', @x, @y, 'RDM @Name_OldValue should not equal @Name_NewValue; should be random new.')
	-- return(1)
end

if (@UserName_OldValue = @UserName_NewValue)
begin
	set @x = convert(varchar(100), @UserName_OldValue)
	set @y = convert(varchar(100), @UserName_NewValue)
	raiserror(505070, 16, 2, 'AreNotEqual', @x, @y, 'RDM @UserName_OldValue should not equal @UserName_NewValue; should be random new.')
	-- return(1)
end

if (@SecurityRole_OldValue = @SecurityRole_NewValue)
begin
	set @x = convert(varchar(100), @SecurityRole_OldValue)
	set @y = convert(varchar(100), @SecurityRole_NewValue)
	raiserror(505070, 16, 2, 'AreNotEqual', @x, @y, 'RDM @SecurityRole_OldValue should not equal @SecurityRole_NewValue; should be random new.')
	-- return(1)
end

if (@Password_OldValue = @Password_NewValue)
begin
	set @x = convert(varchar(100), @Password_OldValue)
	set @y = convert(varchar(100), @Password_NewValue)
	raiserror(505070, 16, 2, 'AreNotEqual', @x, @y, 'RDM @Password_OldValue should not equal @Password_NewValue; should be random new.')
	-- return(1)
end

if (@Enabled_OldValue = @Enabled_NewValue)
begin
	set @x = convert(varchar(100), @Enabled_OldValue)
	set @y = convert(varchar(100), @Enabled_NewValue)
	raiserror(505070, 16, 2, 'AreNotEqual', @x, @y, 'RDM @Enabled_OldValue should not equal @Enabled_NewValue; should be random new.')
	-- return(1)
end

-- update the record with the updates

execute up_UtilityContact_Update
	 @Utility_ContactId = @Utility_ContactId_OldValue
	,@UtilityFK = @UtilityFK_NewValue
	,@Utility_Contact_TypeFK = @Utility_Contact_TypeFK_NewValue
	,@Name = @Name_NewValue
	,@UserName = @UserName_NewValue
	,@SecurityRole = @SecurityRole_NewValue
	,@Password = @Password_NewValue
	,@Enabled = @Enabled_NewValue

-- bail on error

if (NOT @@error = 0)
	return(1)

-- test table count calling the table directly

select @tablecount = count(*) from tb_Utility_Contacts

if (NOT @tablecount = 2)
begin
	set @x = convert(varchar(100), 2)
	set @y = convert(varchar(100), @tablecount)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'tb_Utility_Contacts should contain 2 records, unchanged after Update.')
	-- return(1)
end

-- test table count calling the view

select @viewcount = count(*) from v_tb_Utility_Contacts

if (NOT @viewcount = 2)
begin
	set @x = convert(varchar(100), 2)
	set @y = convert(varchar(100), @viewcount)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'v_tb_Utility_Contacts should contain 2 records, unchanged after Update.')
	-- return(1)
end

-- hold the new values as the old values, so we can test the current record (the new) with what we expect (the old)

set @UtilityFK_OldValue = @UtilityFK_NewValue
set @Utility_Contact_TypeFK_OldValue = @Utility_Contact_TypeFK_NewValue
set @Name_OldValue = @Name_NewValue
set @UserName_OldValue = @UserName_NewValue
set @SecurityRole_OldValue = @SecurityRole_NewValue
set @Password_OldValue = @Password_NewValue
set @Enabled_OldValue = @Enabled_NewValue

-- fetch the current record into the new variables

select top 1
	 @UtilityFK_NewValue = UtilityFK
	,@Utility_Contact_TypeFK_NewValue = Utility_Contact_TypeFK
	,@Name_NewValue = Name
	,@UserName_NewValue = UserName
	,@SecurityRole_NewValue = SecurityRole
	,@Password_NewValue = Password
	,@Enabled_NewValue = Enabled
from tb_Utility_Contacts
where Utility_ContactId = @Utility_ContactId_OldValue

-- check values after update


if (NOT @UtilityFK_OldValue = @UtilityFK_NewValue)
begin
	set @x = convert(varchar(100), @UtilityFK_OldValue)
	set @y = convert(varchar(100), @UtilityFK_NewValue)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'tb_Utility_Contacts.UtilityFK value should not match original value after Update.')
	-- return(1)
end

if (NOT @Utility_Contact_TypeFK_OldValue = @Utility_Contact_TypeFK_NewValue)
begin
	set @x = convert(varchar(100), @Utility_Contact_TypeFK_OldValue)
	set @y = convert(varchar(100), @Utility_Contact_TypeFK_NewValue)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'tb_Utility_Contacts.Utility_Contact_TypeFK value should not match original value after Update.')
	-- return(1)
end

if (NOT @Name_OldValue = @Name_NewValue)
begin
	set @x = convert(varchar(100), @Name_OldValue)
	set @y = convert(varchar(100), @Name_NewValue)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'tb_Utility_Contacts.Name value should not match original value after Update.')
	-- return(1)
end

if (NOT @UserName_OldValue = @UserName_NewValue)
begin
	set @x = convert(varchar(100), @UserName_OldValue)
	set @y = convert(varchar(100), @UserName_NewValue)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'tb_Utility_Contacts.UserName value should not match original value after Update.')
	-- return(1)
end

if (NOT @SecurityRole_OldValue = @SecurityRole_NewValue)
begin
	set @x = convert(varchar(100), @SecurityRole_OldValue)
	set @y = convert(varchar(100), @SecurityRole_NewValue)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'tb_Utility_Contacts.SecurityRole value should not match original value after Update.')
	-- return(1)
end

if (NOT @Password_OldValue = @Password_NewValue)
begin
	set @x = convert(varchar(100), @Password_OldValue)
	set @y = convert(varchar(100), @Password_NewValue)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'tb_Utility_Contacts.Password value should not match original value after Update.')
	-- return(1)
end

if (NOT @Enabled_OldValue = @Enabled_NewValue)
begin
	set @x = convert(varchar(100), @Enabled_OldValue)
	set @y = convert(varchar(100), @Enabled_NewValue)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'tb_Utility_Contacts.Enabled value should not match original value after Update.')
	-- return(1)
end

-- update only some columns (ordinally even columns)

print 'Start Test Update Some'

-- generate random new data to update only a few columns

set @Utility_Contact_TypeFK_NewValue = 687284942
set @UserName_NewValue = 'AA4VAO6AA123HSY4AA6JYA6AAA0JAAAE2MLRGM7AA20A5A688A'
set @Password_NewValue = '2D6W2TODAF07YVCWAYAA64JAPT83AA2A0AVU37H61RJAAAHM5A'

-- ensure that the random new data is different


if (@Utility_Contact_TypeFK_OldValue = @Utility_Contact_TypeFK_NewValue)
begin
	set @x = convert(varchar(100), @Utility_Contact_TypeFK_OldValue)
	set @y = convert(varchar(100), @Utility_Contact_TypeFK_NewValue)
	raiserror(505070, 16, 2, 'AreNotEqual', @x, @y, 'RDM @Utility_Contact_TypeFK_OldValue should not equal @Utility_Contact_TypeFK_NewValue; should be random new.')
	-- return(1)
end

if (@UserName_OldValue = @UserName_NewValue)
begin
	set @x = convert(varchar(100), @UserName_OldValue)
	set @y = convert(varchar(100), @UserName_NewValue)
	raiserror(505070, 16, 2, 'AreNotEqual', @x, @y, 'RDM @UserName_OldValue should not equal @UserName_NewValue; should be random new.')
	-- return(1)
end

if (@Password_OldValue = @Password_NewValue)
begin
	set @x = convert(varchar(100), @Password_OldValue)
	set @y = convert(varchar(100), @Password_NewValue)
	raiserror(505070, 16, 2, 'AreNotEqual', @x, @y, 'RDM @Password_OldValue should not equal @Password_NewValue; should be random new.')
	-- return(1)
end

-- update the db record with the partial updates

execute up_UtilityContact_Update
	 @Utility_ContactId = @Utility_ContactId_OldValue
	,@Utility_Contact_TypeFK = @Utility_Contact_TypeFK_NewValue
	,@UserName = @UserName_NewValue
	,@Password = @Password_NewValue

-- bail on error

if (NOT @@error = 0)
	return(1)

-- fetch current db record values into the new variable

select top 1
	 @UtilityFK_NewValue = UtilityFK
	,@Utility_Contact_TypeFK_NewValue = Utility_Contact_TypeFK
	,@Name_NewValue = Name
	,@UserName_NewValue = UserName
	,@SecurityRole_NewValue = SecurityRole
	,@Password_NewValue = Password
	,@Enabled_NewValue = Enabled
from tb_Utility_Contacts
where Utility_ContactId = @Utility_ContactId_OldValue

-- test the values match or do not match as expected


if (NOT @UtilityFK_OldValue = @UtilityFK_NewValue)
begin
	set @x = convert(varchar(100), @UtilityFK_OldValue)
	set @y = convert(varchar(100), @UtilityFK_NewValue)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'tb_Utility_Contacts.UtilityFK value should be same after not included in Update.')
	-- return(1)
end

if (@Utility_Contact_TypeFK_OldValue = @Utility_Contact_TypeFK_NewValue)
begin
	set @x = convert(varchar(100), @Utility_Contact_TypeFK_OldValue)
	set @y = convert(varchar(100), @Utility_Contact_TypeFK_NewValue)
	raiserror(505070, 16, 2, 'AreNotEqual', @x, @y, 'tb_Utility_Contacts.Utility_Contact_TypeFK value should be new after Update.')
	-- return(1)
end

if (NOT @Name_OldValue = @Name_NewValue)
begin
	set @x = convert(varchar(100), @Name_OldValue)
	set @y = convert(varchar(100), @Name_NewValue)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'tb_Utility_Contacts.Name value should be same after not included in Update.')
	-- return(1)
end

if (@UserName_OldValue = @UserName_NewValue)
begin
	set @x = convert(varchar(100), @UserName_OldValue)
	set @y = convert(varchar(100), @UserName_NewValue)
	raiserror(505070, 16, 2, 'AreNotEqual', @x, @y, 'tb_Utility_Contacts.UserName value should be new after Update.')
	-- return(1)
end

if (NOT @SecurityRole_OldValue = @SecurityRole_NewValue)
begin
	set @x = convert(varchar(100), @SecurityRole_OldValue)
	set @y = convert(varchar(100), @SecurityRole_NewValue)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'tb_Utility_Contacts.SecurityRole value should be same after not included in Update.')
	-- return(1)
end

if (@Password_OldValue = @Password_NewValue)
begin
	set @x = convert(varchar(100), @Password_OldValue)
	set @y = convert(varchar(100), @Password_NewValue)
	raiserror(505070, 16, 2, 'AreNotEqual', @x, @y, 'tb_Utility_Contacts.Password value should be new after Update.')
	-- return(1)
end

if (NOT @Enabled_OldValue = @Enabled_NewValue)
begin
	set @x = convert(varchar(100), @Enabled_OldValue)
	set @y = convert(varchar(100), @Enabled_NewValue)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'tb_Utility_Contacts.Enabled value should be same after not included in Update.')
	-- return(1)
end

print 'Start Test Delete'

-- test delete

execute up_UtilityContact_Delete
	@Utility_ContactId = @Utility_ContactId_OldValue

-- bail on error

if (NOT @@error = 0)
	return(1)

select @tablecount = count(*) from tb_Utility_Contacts

if (NOT @tablecount = 1)
begin
	set @x = convert(varchar(100), 1)
	set @y = convert(varchar(100), @tablecount)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'tb_Utility_Contacts should contain 1 record after Delete.')
	-- return(1)
end

select @viewcount = count(*) from v_tb_Utility_Contacts

if (NOT @viewcount = 1)
begin
	set @x = convert(varchar(100), 1)
	set @y = convert(varchar(100), @viewcount)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'v_tb_Utility_Contacts should contain 1 record after Delete.')
	-- return(1)
end

-- test table count calling the table directly

select @tablecount = count(*) from tb_Utility_Contacts where Utility_ContactId = @Utility_ContactId_OldValue

if (NOT @tablecount = 0)
begin
	set @x = convert(varchar(100), 0)
	set @y = convert(varchar(100), @tablecount)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'tb_Utility_Contacts should contain 0 records when selecting Deleted record.')
	-- return(1)
end

-- test table count calling the view

select @viewcount = count(*) from v_tb_Utility_Contacts where Utility_ContactId = @Utility_ContactId_OldValue

if (NOT @viewcount = 0)
begin
	set @x = convert(varchar(100), 0)
	set @y = convert(varchar(100), @viewcount)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'v_tb_Utility_Contacts should contain 0 records when selecting Deleted record.')
	-- return(1)
end

print 'Start Test TestData Procedure'

-- test test data

execute DevOnly_UtilityContact_UnitTestTestData

-- bail on error

if (NOT @@error = 0)
	return(1)

-- test table count calling the table directly

select @tablecount = count(*) from tb_Utility_Contacts

if (@tablecount = 0)
begin
	set @x = convert(varchar(100), 0)
	set @y = convert(varchar(100), @tablecount)
	raiserror(505070, 16, 2, 'AreNotEqual', @x, @y, 'tb_Utility_Contacts should not contain 0 records after TestData.')
	-- return(1)
end

-- test table count calling the view

select @viewcount = count(*) from v_tb_Utility_Contacts

if (@viewcount = 0)
begin
	set @x = convert(varchar(100), 0)
	set @y = convert(varchar(100), @viewcount)
	raiserror(505070, 16, 2, 'AreNotEqual', @x, @y, 'v_tb_Utility_Contacts should contain 0 records after TestData.')
	-- return(1)
end

print 'Start Test Clear Again'

-- test clear again

execute DevOnly_UtilityContact_UnitTestClear

-- bail on error

if (NOT @@error = 0)
	return(1)

-- test table count calling the table directly

select @tablecount = count(*) from tb_Utility_Contacts

if (NOT @tablecount = 0)
begin
	set @x = convert(varchar(100), 0)
	set @y = convert(varchar(100), @tablecount)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'tb_Utility_Contacts should contain 0 records after Clear.')
	-- return(1)
end

-- test table count calling the view

select @viewcount = count(*) from v_tb_Utility_Contacts

if (NOT @viewcount = 0)
begin
	set @x = convert(varchar(100), 0)
	set @y = convert(varchar(100), @viewcount)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'v_tb_Utility_Contacts should contain 0 records after Clear.')
	-- return(1)
end

print 'CleanUp Unit Test'

execute DevOnly_UtilityContact_UnitTestCleanUp

print 'END UNIT TEST'

GO


